"""
Case Type   : 防篡改
Case Name   : 验证超级用户和表空间的owner可以将表空间的权限授权给其他用户，并且可以回收
Create At   : 2022/3/23
Owner       : @daiguatutu
Description :
    1.设置参数并重启数据库enableseparationofduty=on
    2.创建sysadmin用户和2个普通用户
    3.sysadmin用户连接,创建表空间,将表空间的all权限赋予普通用户1
    4.普通用户1连接,将表空间的all权限赋予普通用户2
    5.普通用户1连接,创建普通表
    6.普通用户2连接,创建临时表
    7.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.创建失败
    6.创建成功
    7.清理环境成功
History     :
"""

import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import macro


class Security(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.u_name1 = 'u_security_create_privilege_0006_01'
        self.u_name2 = 'u_security_create_privilege_0006_02'
        self.u_name3 = 'u_security_create_privilege_0006_03'
        self.ts_name = 'ts_security_create_privilege_0006'
        self.t_name = 't_security_create_privilege_0006'
        self.default_value1 = self.common.show_param('enableSeparationOfDuty')

    def test_security(self):
        text = '-----step1：设置参数并重启数据库enableSeparationOfDuty=on; ' \
               'expect:成功-----'
        self.log.info(text)
        mod_msg = self.pri_sh.execute_gsguc('set',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            'enableSeparationOfDuty=on')
        self.assertTrue(mod_msg, '执行失败:' + text)
        result = self.pri_sh.restart_db_cluster()
        self.assertTrue(result, '执行失败' + text)

        text = '-----step2：创建sysadmin用户和2个普通用户 expect:成功-----'
        self.log.info(text)
        create_cmd = f"drop tablespace if exists {self.ts_name};" \
            f"drop user if exists {self.u_name1},{self.u_name2}," \
            f"{self.u_name3} cascade; " \
            f"create user {self.u_name1} sysadmin password " \
            f"'{macro.COMMON_PASSWD}';" \
            f"create user {self.u_name2} password '{macro.COMMON_PASSWD}';" \
            f"create user {self.u_name3} password '{macro.COMMON_PASSWD}';"
        msg = self.pri_sh.execut_db_sql(create_cmd)
        self.log.info(msg)
        self.assertIn(self.constant.TABLESPCE_DROP_SUCCESS, msg,
                      '执行失败:' + text)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, msg,
                      '执行失败:' + text)
        self.assertEqual(3, msg.count(self.constant.CREATE_ROLE_SUCCESS_MSG),
                         '执行失败:' + text)

        text = '-----step3：sysadmin用户连接,创建表空间,将表空间的all权限赋予普通用户1; ' \
               'expect:成功-----'
        self.log.info(text)
        sql_cmd = f"drop tablespace if exists {self.ts_name};" \
            f"create tablespace {self.ts_name} relative " \
            f"location '{self.ts_name}';" \
            f"grant all on tablespace {self.ts_name} " \
            f"to {self.u_name2} with grant option;"
        connect_sys = f'-U {self.u_name1} -W {macro.COMMON_PASSWD}'
        msg = self.pri_sh.execut_db_sql(sql_cmd, sql_type=connect_sys)
        self.log.info(msg)
        self.assertIn(self.constant.TABLESPCE_DROP_SUCCESS, msg,
                      '执行失败:' + text)
        self.assertIn(self.constant.TABLESPCE_CREATE_SUCCESS, msg,
                      '执行失败:' + text)
        self.assertIn(self.constant.GRANT_SUCCESS_MSG, msg,
                      '执行失败:' + text)

        text = '-----step4：普通用户1连接,将表空间的all权限赋予普通用户2; expect:成功-----'
        self.log.info(text)
        create_cmd = f"grant all on tablespace {self.ts_name} " \
            f"to {self.u_name3} with grant option;"
        connect_nor1 = f'-U {self.u_name2} -W {macro.COMMON_PASSWD}'
        msg = self.pri_sh.execut_db_sql(create_cmd, sql_type=connect_nor1)
        self.log.info(msg)
        self.assertIn(self.constant.GRANT_SUCCESS_MSG, msg, '执行失败:' + text)

        text = '-----step5：普通用户1连接,创建普通表; expect:创建失败-----'
        self.log.info(text)
        create_cmd = f"drop table if exists public.{self.t_name}; " \
            f"create table public.{self.t_name}(id int primary key," \
            f"name varchar(100)) tablespace {self.ts_name};"
        msg = self.pri_sh.execut_db_sql(create_cmd, sql_type=connect_nor1)
        self.log.info(msg)
        self.assertIn('ERROR:  permission denied', msg, '执行失败:' + text)

        text = '-----step6：普通用户2连接,创建临时表; expect:创建成功-----'
        self.log.info(text)
        create_cmd = f"drop table if exists {self.t_name}; " \
            f"create local temp table {self.t_name}(" \
            f"id int primary key,name varchar(100)) " \
            f"tablespace {self.ts_name};"
        connect_nor2 = f'-U {self.u_name3} -W {macro.COMMON_PASSWD}'
        msg = self.pri_sh.execut_db_sql(create_cmd, sql_type=connect_nor2)
        self.log.info(msg)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, msg.splitlines()[3],
                      '执行失败:' + text)

    def tearDown(self):
        self.log.info('-----step7：清理环境-----')
        text1 = '-----删除用户 expect:成功-----'
        self.log.info(text1)
        drop_cmd = f"drop tablespace if exists {self.ts_name};" \
            f"drop user if exists {self.u_name1},{self.u_name2}," \
            f"{self.u_name3} cascade;"
        drop_msg = self.pri_sh.execut_db_sql(drop_cmd)
        self.log.info(drop_msg)

        text2 = '-----修改参数enableSeparationOfDuty为初始值 expect:成功-----'
        self.log.info(text2)
        mod_msg1 = self.pri_sh.execute_gsguc('set',
                                             self.constant.GSGUC_SUCCESS_MSG,
                                             f'enableSeparationOfDuty='
                                             f'{self.default_value1}')
        restart_res = self.pri_sh.restart_db_cluster()

        self.log.info('-----断言tearDown执行成功-----')
        self.assertIn(self.constant.TABLESPCE_DROP_SUCCESS, drop_msg,
                      '执行失败:' + text1)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, drop_msg,
                      '执行失败:' + text1)
        self.assertTrue(mod_msg1, '执行失败:' + text2)
        self.assertTrue(restart_res, '执行失败:' + text2)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
